package com.liwen.kefu.service.impl;

import club.kingon.sql.builder.SqlBuilder;
import com.liwen.kefu.common.result.CommonCode;
import com.liwen.kefu.common.result.ResponseResult;
import com.liwen.kefu.configuration.KeFuJdbcConfig;
import com.liwen.kefu.service.JdbcSqlBuildService;
import com.liwen.kefu.service.KeFuDataActionService;
import com.liwen.kefu.utils.DataUtil;
import com.liwen.kefu.vo.BaseDataVo;
import com.liwen.kefu.vo.DataImportVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.SQLException;
import java.time.ZonedDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author: Liwen
 * @createTime: 2024/11/9
 * @description:
 */

@Service
@Slf4j
public class KeFuDataActionServiceImpl implements KeFuDataActionService {

    @Resource(name = "configJdbcTemplate")
    private JdbcTemplate configJdbcTemplate;

    @Resource(name = "hiveJdbcTemplate")
    private JdbcTemplate hiveJdbcTemplate;

    @Autowired
    private JdbcSqlBuildService jdbcSqlBuilderService;

    @Autowired
    private KeFuJdbcConfig keFuJdbcConfig;


    /**
     * 添加hive查询sql
     *
     * @param dataImportVo
     */
    @Override
    public ResponseResult addQuerySql(DataImportVo dataImportVo) throws SQLException {
        String baseTableName = "t_data_import_sql";
        String baseDbName = "db_import_config";
        String querySql = SqlBuilder.selectAll().from(baseTableName).whereEq("table_name", dataImportVo.getTableName()).build();
        List<Map<String, Object>> resultList = configJdbcTemplate.queryForList(querySql);
        HashMap<String, Object> map = new HashMap<>();
        if (StringUtils.isNotEmpty(dataImportVo.getDbName())) {
            map.put("db_name", dataImportVo.getDbName());
        }
        if (StringUtils.isNotEmpty(dataImportVo.getQuerySql())) {
            map.put("query_sql", dataImportVo.getQuerySql());
        }
        if (StringUtils.isNotEmpty(dataImportVo.getDeleteKey())) {
            map.put("delete_key", dataImportVo.getDeleteKey());
        }
        if (StringUtils.isNotEmpty(dataImportVo.getTableName())) {
            map.put("table_name", dataImportVo.getTableName());
        }
        if (StringUtils.isNotEmpty(dataImportVo.getModelName())) {
            map.put("model_name", dataImportVo.getModelName());
        }
        if (StringUtils.isNotEmpty(dataImportVo.getNotes())) {
            map.put("notes", dataImportVo.getNotes());
        }
        if (CollectionUtils.isEmpty(resultList)) {
            keFuJdbcConfig.execAction(baseDbName, jdbcSqlBuilderService.toInsert(baseTableName, map));
            return ResponseResult.SUCCESS(CommonCode.ADD_SUCCESS);
        }
        // 修改
        keFuJdbcConfig.execAction(baseDbName, jdbcSqlBuilderService.toUpdate(baseTableName, map, "table_name"));
        keFuJdbcConfig.close();
        return ResponseResult.SUCCESS(CommonCode.UPDATE_SUCCESS);
    }

    /**
     * 获取配置sql
     *
     * @return
     */
    @Override
    public ResponseResult getConfig(String keyWord) {
        String sql = "";
        if (StringUtils.isEmpty(keyWord)) {
            sql = SqlBuilder.selectAll().from("t_data_import_sql").build();
        } else {
            sql = SqlBuilder.selectAll().from("t_data_import_sql").whereEq("model_name", keyWord)
                    .orLike("table_name", keyWord).orEq("db_name", keyWord).orLike("notes",keyWord).build();
        }
        List<Map<String, Object>> list = configJdbcTemplate.queryForList(sql);
        return ResponseResult.SUCCESS(CommonCode.SUCCESS, list);
    }

    /**
     * 查询userId
     *
     * @param mobileNo
     * @return
     */
    @Override
    public Map<String, Object> queryUserId(String mobileNo) {
        String sql = "select u.id,u.mobile_no from passport.t_user as u left join passport.t_customer c on u.id= c.user_id where c.id_no = '${mobileNo}' or u.mobile_no ='${mobileNo}';".replaceAll("\\$\\{mobileNo\\}", mobileNo);
        log.info("【开始查询userId:{}】=================================", sql);
        Map<String, Object> map = hiveJdbcTemplate.queryForMap(sql);
        log.info("【userId:{}】==============================================", map);
        return map;

    }

    /**
     * 1、查询hive
     * 2、导入mysql
     *
     * @param baseDataVo
     * @return
     */
    @Override
    public ResponseResult hiveImportDataToSql(BaseDataVo baseDataVo) {
        Map<String, String> resultMap = new LinkedHashMap<>();
        List<Map<String, String>> baseList = null;
        try {
            baseList = queryTableListSql(baseDataVo);
        } catch (Exception e) {
            e.printStackTrace();
            resultMap.put("exception", handleExceptionFormat(e));
            return ResponseResult.ERROR(CommonCode.BASE_TABLE_QUERY_FAIL, resultMap);
        }
        int number = 0;
        for (Map<String, String> map : baseList) {
            number++;
            String dbName = map.get("dbName");
            String tableName = map.get("tableName");
            String deleteKey = map.get("deleteKey");
            String sql = map.get("sql");
            String resultKey = number + "、" + dbName + "." + tableName;
            log.info("【开始循环查询hive，第：{}次查询，表：{}:{}】", number, dbName, tableName);
            List<Map<String, Object>> HiveResultList = null;
            try {
                HiveResultList = queryHiveList(sql);
            } catch (Exception e) {
                e.printStackTrace();
                resultMap.put(resultKey, handleExceptionFormat(e));
                return ResponseResult.ERROR(CommonCode.HIVE_QUERY_FAIL, resultMap);
            }
            if (CollectionUtils.isEmpty(HiveResultList)) {
                log.error("【hive查询列表=null】=============================================");
                resultMap.put(resultKey, "hive查询列表=null");
                continue;
            }
            // 根据key获取删除的value
            List<String> deleteValues = null;
            try {
                deleteValues = getDeleteValues(deleteKey, HiveResultList);
            } catch (Exception e) {
                e.printStackTrace();
                resultMap.put(resultKey, handleExceptionFormat(e));
                return ResponseResult.ERROR(CommonCode.GET_VALUES_BY_KEY_FAIL, resultMap);
            }
            try {
                deleteDataKeys(dbName, tableName, deleteKey, deleteValues);
            } catch (SQLException e) {
                e.printStackTrace();
                resultMap.put(resultKey, handleExceptionFormat(e));
                return ResponseResult.ERROR(CommonCode.DELETE_KEYS_FAIL, resultMap);
            }
            // 插入mysql
            try {
                for (Map<String, Object> item : HiveResultList) {
                    keFuJdbcConfig.execAction(dbName, jdbcSqlBuilderService.toInsert(tableName, item));
                }
                resultMap.put(resultKey, "success, " + "size：" + HiveResultList.size());
            } catch (Exception e) {
                e.printStackTrace();
                resultMap.put(resultKey, handleExceptionFormat(e));
                return ResponseResult.ERROR(CommonCode.INSERT_INTO_FAIL, resultMap);
            }
        }
        keFuJdbcConfig.close();

        ZonedDateTime endZdt = ZonedDateTime.now();
        number++;
        String timeConsuming = DataUtil.getTimeConsuming(baseDataVo.getStartZdt(), endZdt);
        resultMap.put(number + "、耗时", timeConsuming);
        log.info("【操作完成,耗时：{}】=============================================", timeConsuming);
        return ResponseResult.SUCCESS(CommonCode.SUCCESS, resultMap);
    }


    /**
     * 根据key 删除测试环境
     *
     * @param dbName
     * @param tableName
     * @param key
     * @param valueList
     * @throws SQLException
     */
    public int deleteDataKeys(String dbName, String tableName, String key, List<String> valueList) throws SQLException {
        log.info("【开始删除mysql测试环境：{}.{}】", dbName, tableName);
        String sql = jdbcSqlBuilderService.toDelete(tableName, key, valueList);
        return keFuJdbcConfig.execAction(dbName, sql);
    }

    /**
     * 根据key从hive中获取要删除mysql的value
     *
     * @param key
     * @param hiveResultList
     * @return
     */
    public List<String> getDeleteValues(String key, List<Map<String, Object>> hiveResultList) {
        List<String> valueList = hiveResultList.stream().map(item -> {
            String value = item.get(key) + "";
            return value;
        }).distinct().collect(Collectors.toList());
        return valueList;
    }

    /**
     * 获取hive 列表
     *
     * @param sql
     * @return
     */

    public List<Map<String, Object>> queryHiveList(String sql) {
        log.info("【hive查询sql】:{}", sql);
        List<Map<String, Object>> list = hiveJdbcTemplate.queryForList(sql);
        return list;
    }

    /**
     * 获取mysql所有的库、表、查询sql
     */
    public List<Map<String, String>> queryTableListSql(BaseDataVo baseDataVo) {
        String querySql = "";
        if (StringUtils.isNotEmpty(baseDataVo.getTableName())) {
            querySql = SqlBuilder.selectAll().from("t_data_import_sql").whereEq("table_name", baseDataVo.getTableName()).andEq("disable", 1).orderByAsc("id").build();
        } else {
            querySql = SqlBuilder.selectAll().from("t_data_import_sql").whereEq("disable", 1).orderByAsc("id").build();
        }
        List<Map<String, Object>> list = configJdbcTemplate.queryForList(querySql);
        List<Map<String, String>> formatList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(list)) {
            for (Map<String, Object> map : list) {
                String dbName = (String) map.get("db_name");
                String tableName = (String) map.get("table_name");
                String tableSql = (String) map.get("query_sql");
                String deleteKey = (String) map.get("delete_key");
                String modelName = (String) map.get("model_name");
                String formatSql = tableSql.replaceAll("\\$\\{mobile_no\\}", baseDataVo.getMobileNo()).replaceAll("\\$\\{user_id\\}", baseDataVo.getUserId());
                HashMap<String, String> formatMap = new HashMap<>();
                formatMap.put("dbName", dbName);
                formatMap.put("tableName", tableName);
                formatMap.put("sql", formatSql);
                formatMap.put("deleteKey", deleteKey);
                formatMap.put("modelName", modelName);
                formatList.add(formatMap);
            }
        }

        List<Map<String, String>> resultFormatList = new ArrayList<>();
        if (StringUtils.isEmpty(baseDataVo.getTableName())) {
            // 会员
            if (StringUtils.isNotEmpty(baseDataVo.getModelName()) && "emc".contains(baseDataVo.getModelName())) {
                resultFormatList = formatList.stream()
                        .filter(item -> item.get("modelName").equals("emc")).collect(Collectors.toList());
            }
            // 新核心
            if (StringUtils.isNotEmpty(baseDataVo.getModelName()) && "new".contains(baseDataVo.getModelName())) {
                resultFormatList = formatList.stream()
                        .filter(item -> item.get("modelName").equals("new")).collect(Collectors.toList());
            }
            // 老核心
            if (StringUtils.isNotEmpty(baseDataVo.getModelName()) && "old".contains(baseDataVo.getModelName())) {
                resultFormatList = formatList.stream()
                        .filter(item -> item.get("modelName").equals("old")).collect(Collectors.toList());
            }
            // yika: 新核心 + 老核心
            if (StringUtils.isNotEmpty(baseDataVo.getModelName()) && "yika".contains(baseDataVo.getModelName())) {
                resultFormatList = formatList.stream()
                        .filter(item -> item.get("modelName").equals("old") || item.get("modelName").equals("new"))
                        .collect(Collectors.toList());
            }
        } else {
            resultFormatList = formatList.stream().collect(Collectors.toList());
        }
        return resultFormatList;
    }


    /**
     * 处理异常 格式化
     *
     * @param e
     * @return
     */
    @Override
    public String handleExceptionFormat(Exception e) {
        StringWriter sw = new StringWriter();
        e.printStackTrace(new PrintWriter(sw, true));
        String info = sw.toString().replaceAll("\r", "").replaceAll("\t", "").replaceAll("\n", "").replaceAll("tat", "");
        return info;
    }

    /**
     * 批量、单表删除
     *
     * @param baseDataVo
     * @return
     */
    /*@Override
    public ResponseResult deleteDataByMobilNo(BaseDataVo baseDataVo) {
        List<Map<String, String>> tableList = queryTableListSql(baseDataVo);
        int num = 0;
        Map<String, String> resultMap = new LinkedHashMap<>();
        for (Map<String, String> map : tableList) {
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }

            num++;
            String dbName = map.get("dbName");
            String tableName = map.get("tableName");
            String deleteKey = map.get("deleteKey");
            String sql = map.get("sql");
            String resultKey = num + "、" + dbName + "." + tableName;
            log.info("【开始循环查询hive，第：{}次查询，表：{}:{}】", num, tableName, tableName);
            List<Map<String, Object>> HiveResultList = queryHiveList(sql);
            if (CollectionUtils.isEmpty(HiveResultList)) {
                log.error("【hive查询列表=null】=============================================");
                resultMap.put(resultKey, "hive查询列表=null");
                continue;
            }
            // 根据key获取删除的value
            List<String> deleteValues = getDeleteValues(deleteKey, HiveResultList);
            int count = 0;
            try {
                count = deleteDataKeys(dbName, tableName, deleteKey, deleteValues);
            } catch (SQLException e) {
                e.printStackTrace();
                resultMap.put(resultKey, handleExceptionFormat(e));
                return ResponseResult.ERROR(CommonCode.DELETE_KEYS_FAIL, resultMap);
            }
            resultMap.put(resultKey, "success, count: " + count);
        }
        keFuJdbcConfig.close();
        log.info("【删除完成】=============================================");
        return ResponseResult.SUCCESS(CommonCode.SUCCESS, resultMap);
    }*/
}
